﻿CREATE TABLE [dbo].[ContentItem] (
    [ID]            UNIQUEIDENTIFIER   NOT NULL,
    [Title]         NVARCHAR (200)     NOT NULL,
    [Caption]       NVARCHAR (1000)    NOT NULL,
    [Topic]         NVARCHAR (200)     NOT NULL,
    [MediaTypeID]   UNIQUEIDENTIFIER   NOT NULL,
    [ThresholdID]   UNIQUEIDENTIFIER   NOT NULL,
    [RegimeID]      UNIQUEIDENTIFIER   NOT NULL,
    [MediaBlobURL]  NVARCHAR (500)     NOT NULL,
    [TimeUnitID]    UNIQUEIDENTIFIER   NOT NULL,
    [ContentDate]   DATETIMEOFFSET (7) NULL,
    [ContentYear]   DECIMAL (8, 4)     NULL,
    [MediaSource]   NVARCHAR (300)     NULL,
    [Attribution]   NVARCHAR (200)     NULL,
    [LicenseTypeID] UNIQUEIDENTIFIER   NOT NULL,
    [CreatedOn]     SMALLDATETIME      CONSTRAINT [DF_ContentItem_CreatedOn] DEFAULT (CONVERT([smalldatetime],getdate(),(0))) NULL,
    [CreatedBy]     UNIQUEIDENTIFIER   NULL,
    [ModifiedOn]    SMALLDATETIME      CONSTRAINT [DF_ContentItem_ModifiedOn] DEFAULT (CONVERT([smalldatetime],getdate(),(0))) NULL,
    [ModifiedBy]    UNIQUEIDENTIFIER   NULL,
    [IsVisible]     BIT                NULL,
    [IsDeleted]     BIT                NULL,
    [UniqueID]      INT                IDENTITY (1, 1) NOT NULL,
    [CurrVersion]   INT                NULL,
    CONSTRAINT [PK_ContentItem] PRIMARY KEY CLUSTERED ([ID] ASC),
    FOREIGN KEY ([CurrVersion]) REFERENCES [dbo].[CZVersion] ([VersionNumber]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [FK_ContentItem_LicenseType] FOREIGN KEY ([LicenseTypeID]) REFERENCES [dbo].[LicenseType] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [FK_ContentItem_MediaType] FOREIGN KEY ([MediaTypeID]) REFERENCES [dbo].[MediaType] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [FK_ContentItem_Regime] FOREIGN KEY ([RegimeID]) REFERENCES [dbo].[Regime] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [FK_ContentItem_Threshold] FOREIGN KEY ([ThresholdID]) REFERENCES [dbo].[Threshold] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [FK_ContentItem_TimeUnit] FOREIGN KEY ([TimeUnitID]) REFERENCES [dbo].[TimeUnit] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [FK_ContentItem_User] FOREIGN KEY ([CreatedBy]) REFERENCES [dbo].[User] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [FK_ContentItem_User1] FOREIGN KEY ([ModifiedBy]) REFERENCES [dbo].[User] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION
);

